package br.com.jw.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import br.com.jw.jdbc.Pool;
import br.com.jw.modelo.Cliente;

public class ClienteDao{
	Connection conn;
	PreparedStatement stmt;
	private Pool pool;
	int resultado =0;
	
	public ClienteDao(Pool pool){
		this.pool=pool;
	}
	
	public boolean ativarDesativarCliente(int cli_id, String cli_status) throws ClassNotFoundException, SQLException{
		String sql="update cliente set cli_status=? where cli_id=?";
		try{
			conn=pool.getConnection();
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, cli_status);
			stmt.setInt(2, cli_id);
			stmt.execute();
			
			stmt.close();
			return true;
			
		}finally{
			pool.liberarConexao(conn);
		}
	}
	public boolean existeCnpj(Cliente cliente) throws ClassNotFoundException, SQLException{
	    boolean resultado = false;
		String sql="select * from cliente where cli_cnpj=?";
		conn=pool.getConnection();
		try{
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, cliente.getCli_cnpj());
			ResultSet rs=stmt.executeQuery();
			if(rs.next()){
				resultado = true;
			}else{
				resultado = false;
			}
			return resultado;
		}finally{
			pool.liberarConexao(conn);
		}
	}
	public int inserirCliente(Cliente cliente) throws ClassNotFoundException, SQLException {

		String sql = "insert into cliente (cli_nome,cli_telefone,cli_cnpj,cli_status)values (?,?,?,?)";
		conn = pool.getConnection();
		try {
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, cliente.getCli_nome());
			stmt.setString(2, cliente.getCli_telefone());
			stmt.setString(3, cliente.getCli_cnpj());
			stmt.setString(4, cliente.getCli_status());

			resultado =stmt.executeUpdate();
			
			stmt.close();
			return resultado;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			pool.liberarConexao(conn);
		}
		return resultado;
	}
	
	public ArrayList<Cliente> listClientes() throws ClassNotFoundException, SQLException{
	
		ArrayList<Cliente> listaClientes=new ArrayList<>();
		String sql="select * from cliente";
		conn = pool.getConnection();
		try{
			    stmt=conn.prepareStatement(sql);
				ResultSet rs=stmt.executeQuery();
				
					while(rs.next()){
						listaClientes.add(new Cliente(
								rs.getInt("cli_id"),
								rs.getString("cli_nome"),
								rs.getString("cli_telefone"),
								rs.getString("cli_cnpj"),
								rs.getString("cli_status")
								
								));				
					}
					
					stmt.close();
					rs.close();
					return listaClientes;
				
			
		}finally{
			pool.liberarConexao(conn);
		}
}

	public boolean excluirCliente(int cli_id) throws ClassNotFoundException, SQLException {

		String sql = "delete from cliente where cli_id=?";
		conn = pool.getConnection();
		try {
			stmt = conn.prepareStatement(sql);

			stmt.setInt(1, cli_id);

		    stmt.execute();		    
		    stmt.close();
			conn.close();
			
			return true;
		} finally{
			pool.liberarConexao(conn);
			}
	}
	public int alterarCliente(Cliente cliente) throws ClassNotFoundException, SQLException {
        
		String sql = "update cliente set cli_nome=?,cli_telefone=?, cli_cnpj=? where cli_id=?";
		conn = pool.getConnection();
		try {
			stmt = conn.prepareStatement(sql);
						
			stmt.setString(1, cliente.getCli_nome());
			stmt.setString(2, cliente.getCli_telefone());
			stmt.setString(3, cliente.getCli_cnpj());	
			//stmt.setString(4, cliente.getCli_status());
			stmt.setInt(4, cliente.getCli_id());
			
		    resultado = stmt.executeUpdate();
		    
		    stmt.close();
			return resultado;
			
		} finally{
			pool.liberarConexao(conn);
		}
}	
	}



